﻿using AppDataStruct;
using ComLib;
using LogLib;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using ZXing;
using ZXing.Common;

namespace PrintExcel
{
    public class WriteData : IDisposable
    {
        public string AssemblyDirectory
        {
            get
            {
                string directory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
                directory = directory.Substring(6);
                return directory;
            }
        }

        private string _srcFile = "";

        private string _destFile = "";
        public string DestFile
        {
            get { return _destFile; }
        }

        private FileStream fs = null;

        private OutputDirectory _dirConfig = new OutputDirectory();

        private HSSFWorkbook workbook = null;

        private HSSFSheet sheet = null;

        private int RowsCount = 0;

        private int ColumnCount = 0;

        private ExcelData _dataSet = null;

        private string _todaySavePath = "";

        private string _dataSavePath = "";

        private Bitmap sfzBackImage = null;

        private Bitmap sfzFrontImage = null;

        private string _error = "";
        public string ErrorMessage
        {
            get { return _error; }
        }

        public WriteData(ExcelData data)
        {
            _dataSet = data;
            LoadConfig();
        }

        private void LoadConfig()
        {
            if(!File.Exists(_dirConfig.ConfigFilePath))
            {
                _dirConfig.Save();
            }
            _dirConfig.Load();
        }

        public void Dispose()
        {
            try
            {
                if (workbook != null)
                {
                    workbook.Dispose();
                    workbook = null;
                    Log.WriteAsync("资源已释放...");
                }
            }
            catch (Exception e)
            {
                Log.WriteAsync(e.Message);
            }
            try
            {
                if(sfzBackImage != null)
                {
                    sfzBackImage.Dispose();
                    sfzBackImage = null;
                }
                if (sfzFrontImage != null)
                {
                    sfzFrontImage.Dispose();
                    sfzFrontImage = null;
                }
            }
            catch (Exception e)
            {
                Log.WriteAsync(e.Message);
            }
        }

        private void MergePackageImage(SFZInfo sfzInfo)
        {
            try
            {
                Log.WriteAsync("开始读取身份证正反面图片");
                SFZMerge merge = new SFZMerge(sfzInfo);
                if (!string.IsNullOrWhiteSpace(sfzInfo.SFZZM) && File.Exists(sfzInfo.SFZZM))
                {
                    Log.WriteAsync($"身份证正面[{sfzInfo.SFZZM}]文件存在");

                    System.IO.FileStream fs = new System.IO.FileStream(sfzInfo.SFZZM, FileMode.Open, FileAccess.Read);
                    sfzFrontImage = new Bitmap(System.Drawing.Image.FromStream(fs));
                    fs.Close();                    
                }
                else
                {
                    Log.WriteAsync($"文件[{sfzInfo.SFZZM}]不存在，开始生成身份证正面");
                    //sfzFrontImage = merge.MergeSFZFrontPicture();
                }

                if (!string.IsNullOrWhiteSpace(sfzInfo.SFZBM) && File.Exists(sfzInfo.SFZBM))
                {
                    Log.WriteAsync($"身份证背面[{sfzInfo.SFZBM}]文件存在");

                    System.IO.FileStream fs = new System.IO.FileStream(sfzInfo.SFZBM, FileMode.Open, FileAccess.Read);
                    sfzBackImage = new Bitmap(System.Drawing.Image.FromStream(fs));
                    fs.Close();
                }
                else
                {
                    Log.WriteAsync($"文件[{sfzInfo.SFZBM}]不存在，开始生成身份证背面");
                    //sfzBackImage = merge.MergeSFZBackPicture();
                }
            }
            catch (Exception ex)
            {
                Log.WriteException(ex.ToString());
            }            
        }

        public bool Init()
        {            
            _srcFile = string.Format("{0}\\config\\中国公民出入境证件申请表-模板.xls", AssemblyDirectory);
            if (!File.Exists(_srcFile))
            {
                _error = "Print Excel model file not exists:" + _srcFile;
            }           
            return true;
        }

        private bool OnDataPrepared(SFZInfo sfzInfo)
        {
            Log.WriteAsync("准备生成Excel data...");
            _todaySavePath = _dirConfig[sfzInfo.SFZH];
            _destFile = Path.Combine(_todaySavePath, $"ApplyData_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls");
            if (File.Exists(_destFile))
            {
                try
                {
                    File.Delete(_destFile);
                }
                catch (Exception ex)
                {
                    Log.WriteAsync(ex.Message);
                }
            }
            int retry_times = 0;
            Recopy:
            try
            {
                File.Copy(_srcFile, _destFile, true);
            }
            catch (Exception ex)
            {
                _error = "复制模板文件失败:" + ex.Message;
               

                Log.WriteAsync(_error);
                retry_times++;
                if (retry_times < 3)
                {
                    Log.WriteAsync($"第{retry_times}次重试");
                    _destFile = Path.Combine(_todaySavePath, $"ApplyData_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls");
                    goto Recopy;
                }
                    
                return false;
            }
            using (var fs = new FileStream(_destFile, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook = new HSSFWorkbook(fs);
                sheet = (HSSFSheet)workbook.GetSheetAt(0);
                RowsCount = sheet.LastRowNum;
                IRow headerRow = sheet.GetRow(0);
                ColumnCount = headerRow.LastCellNum;
                //if (!sfzInfo.IsManualInputNumberVerify)
                //{
                    //MergePackageImage(sfzInfo);
                //}                                
            }
            
            Log.WriteAsync("准备完成...");
            return true;
        }

        public bool WriteExcelData(SFZInfo sfzInfo, PreApplyInfo appInfo) 
        {
            try
            {
                Dispose();
                if (!OnDataPrepared(sfzInfo))
                {
                    _destFile = "";
                    return false;
                }
                DataConverter converter = new DataConverter(appInfo, sfzInfo, _dataSet);
                converter.Excute();
                Dictionary<string, string> mapAppInfo = converter.MapData;
                string strLog = "";
                // 写入身份证号码
                int nCount = sfzInfo.SFZH.Length;
                for (int j = 0; j < nCount; j++)
                {
                    string name = string.Format("sfzh_{0}", j + 1);
                    if (_dataSet.ContainsItem(name))
                    {
                        RangeItemInfo item = _dataSet[name];
                        if (!SetRangeText(item, sfzInfo.SFZH, j))
                        {
                            strLog = string.Format("SFZH Write To Excel Fail: index->{0}!", j);
                            Log.WriteAsync(strLog);
                            continue;
                        }
                    }
                    else
                    {
                        strLog = string.Format("The SFZH item are not exists in config file: index->{0}!", j);
                        Log.WriteAsync(strLog);
                    }
                }
                foreach (KeyValuePair<string, string> p in mapAppInfo)
                {
                    string key = p.Key;
                    string value = p.Value;
                    if (!_dataSet.ContainsItem(key))
                    {
                        Log.WriteAsync("配置文件不包含元素名称:" + key);
                        continue;
                    }
                    RangeItemInfo range = _dataSet[key];
                    switch (range.Type)
                    {
                        case ItemType.Text:
                            SetRangeText(range, value);
                            break;
                        case ItemType.CheckBox:
                            if (value.Equals("1"))
                            {
                                SetRangeCheck(range, 1);
                            }
                            break;
                        case ItemType.Picture:
                            //if (key.Equals("sfzzm"))
                            //{
                            //    if (sfzBackImage != null)
                            //    {
                            //        InsertPicture(range, sfzBackImage);
                            //    }
                            //}
                            //else if (key.Equals("sfzbm"))
                            //{
                            //    if (sfzFrontImage != null)
                            //    {
                            //        InsertPicture(range, sfzFrontImage);
                            //    }
                            //}
                            //else
                            //{
                                InsertPicture(range, value);
                            //}
                            break;
                        case ItemType.UndeLine:
                            SetRangeUnderLineText(range, value);
                            break;
                        case ItemType.BarCode:
                            SetBarCode(range, value);
                            break;
                        case ItemType.PackageImage:

                            break;
                        default:
                            break;
                    }
                }
                Log.WriteAsync("Excel data has filled finished.");
                using (FileStream fsw = File.OpenWrite(_destFile))
                {
                    workbook.Write(fsw);
                }
                Log.WriteAsync("The Excel file has saved.");
                return true;
            }
            catch(Exception ex)
            {
                Log.WriteException(ex.ToString());
                return false;
            }
        }

        private Tuple<int, int> ConvertToColumnRowIndex(string name)
        {
            int rowIndex = 0;
            int columnIndex = 0;
            char[] chars = name.ToUpper().ToCharArray();
            int start = -1;
            for (int i = 0; i < chars.Length; i++)
            {
                if (chars[i] >= 'A' && chars[i] <= 'Z')
                {
                    continue;
                }
                if (chars[i] >= '0' && chars[i] <= '9')
                {
                    if (i > 0 && chars[i - 1] >= 'A' && chars[i - 1] <= 'Z')
                    {
                        start = i;
                        break;
                    }
                }
            }
            if (start == -1)
            {
                throw new Exception("Invalid range name");
            }
            string columnName = name.Substring(0, start);
            string rowName = name.Substring(start, name.Length - start);
            rowIndex = int.Parse(rowName) - 1;
            columnIndex = ColumnToIndex(columnName);
            return new Tuple<int, int>(columnIndex, rowIndex);
        }

        /// <summary>
        /// 用于excel表格中列号字母转成列索引，从1对应A开始
        /// </summary>
        /// <param name="column">列号</param>
        /// <returns>列索引</returns>
        private int ColumnToIndex(string column)
        {
            if (!Regex.IsMatch(column.ToUpper(), @"[A-Z]+"))
            {
                throw new Exception("Invalid parameter");
            }
            int index = 0;
            char[] chars = column.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            index -= 1;
            return index;
        }

        /// <summary>
        /// 用于excel表格中列号字母转成列索引，从1对应A开始
        /// </summary>
        /// <param name="name">列号</param>
        /// <returns>列索引</returns>
        private bool CheckRangeNameIsVaild(string name)
        {
            if(string.IsNullOrEmpty(name))
            {
                return false;
            }
            char[] chars = name.ToUpper().ToCharArray();
            bool columnExists = false;
            bool rowExists = false;
            for (int i = 0; i < chars.Length; i++)
            {
                if (chars[i] >= 'A' && chars[i] <= 'Z')
                {
                    if (rowExists)
                    {
                        return false;
                    }
                    if (!columnExists)
                    {
                        columnExists = true;
                    }                    
                    continue;
                }
                else if (chars[i] >= '0' && chars[i] <= '9')
                {
                    if (!columnExists)
                    {
                        return false;
                    }
                    if (!rowExists)
                    {
                        rowExists = true;
                    }
                    continue;
                }
                else
                {
                    return false;
                }
            }
            return rowExists && columnExists;
        }

        public bool SetRangeText(RangeItemInfo item, string value, int iChar = -1)
        {
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }
            string cellValue = value;
            Tuple<int, int> t = ConvertToColumnRowIndex(strStart);
            IRow row = sheet.GetRow(t.Item2);
            ICell cell = row.GetCell(t.Item1);
            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }
            if (iChar != -1)        
            {
                if (value.Length >= iChar)
                {
                    cellValue = value.ElementAt<char>(iChar).ToString();
                }
                else
                {
                    cellValue = "";
                }
            }
            cell.SetCellValue(cellValue);
            strLog = string.Format("Write {0} value {1}", item.Name, cellValue);
            Log.WriteAsync(strLog);
            return true;
        }

        public void SetCellChecked(ICell cell, int nCheckBoxType = 1)
        {
            //string content = "R";
            //string fontName = "Wingdings 2";
            string content = "n";
            string fontName = "Wingdings";
            int fontSize = 16;
            if (nCheckBoxType != 1)
            {
                content = "n";
                fontName = "Wingdings";
                fontSize = 16;
            }
            HSSFRichTextString a = new HSSFRichTextString(content);
            HSSFFont font = (HSSFFont)workbook.CreateFont();
            font.FontName = fontName;
            font.FontHeightInPoints = fontSize;
            a.ApplyFont(font);
            HSSFCellStyle style1 = (HSSFCellStyle)workbook.CreateCellStyle();
            style1.SetFont(font);
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            cell.SetCellValue(a);
        }

        public bool SetRangeCheck(RangeItemInfo item, int nCheckBoxType = 1)
        {
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }

            Tuple<int, int> t = ConvertToColumnRowIndex(strStart);
            IRow row = sheet.GetRow(t.Item2);
            ICell cell = row.GetCell(t.Item1);
            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }
            SetCellChecked(cell, nCheckBoxType);
            strLog = string.Format("Set {0} value checked", item.Name);
            Log.WriteAsync(strLog);
            return true;
        }

        public bool SetRangeUnderLineText(RangeItemInfo item, string InputString)
        {
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }
            Tuple<int, int> t = ConvertToColumnRowIndex(strStart);
            IRow row = sheet.GetRow(t.Item2);
            ICell cell = row.GetCell(t.Item1);
            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }
            cell.SetCellValue(InputString);
            cell.CellStyle.GetFont(workbook).Underline = FontUnderlineType.Single;
            strLog = string.Format("Set {0} value {1} with underline", item.Name, InputString);
            Log.WriteAsync(strLog);
            return true;
        }

        public bool InsertPicture(RangeItemInfo item, string imagePath)
        {
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }

            Tuple<int, int> t0 = ConvertToColumnRowIndex(strStart);
            Tuple<int, int> t1 = ConvertToColumnRowIndex(strEnd);

            IRow row = sheet.GetRow(t0.Item2);
            ICell cell = row.GetCell(t0.Item1);
            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }

            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            if (!File.Exists(imagePath))
            {
                Log.WriteAsync($"照片文件不存在：{imagePath}");
                return false;
            }
            //将图片文件读入一个字符串
            byte[] bytes = System.IO.File.ReadAllBytes(imagePath);
            int pictureIdx = sheet.Workbook.AddPicture(bytes, PictureType.JPEG);

            // 插图片的位置  
            int dx1 = (int)item.OffsetX;
            int dy1 = (int)item.OffsetY; 
            int dx2 = (int)(1023 - item.OffsetX);
            int dy2 = (int)(255 - item.OffsetY);

            int col1 = t0.Item1, row1 = t0.Item2, col2 = t1.Item1, row2 = t1.Item2;
          
            IClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
            //把图片插到相应的位置 IPicture pict = (HSSFPicture)
            patriarch.CreatePicture(anchor, pictureIdx);
            Log.WriteAsync($"写入照片：{imagePath}成功");
            return true;
        }

        public bool SetBarCode(RangeItemInfo item, string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                string logInfo = string.Format("关键字{0}对应的条码值为空.", item.Name);
                Log.WriteAsync(logInfo);
                return false;
            }
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }

            Tuple<int, int> t0 = ConvertToColumnRowIndex(strStart);
            Tuple<int, int> t1 = ConvertToColumnRowIndex(strEnd);

            IRow row = sheet.GetRow(t0.Item2);
            ICell cell = row.GetCell(t0.Item1);
            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }

            //产生二维码
            int nBarWidth = 300;
            int nBarHeight = 50;
            if (item.Width != 0 && item.Height != 0)
            {
                nBarHeight = item.Height;
                nBarWidth = item.Width;
            }
          
            string bmpPath = Path.Combine(_todaySavePath,item.Name + ".jpg");
            try
            {
                if(File.Exists(bmpPath))
                {
                    File.Delete(bmpPath);
                }               
            }
            catch
            {

            }
            Bitmap image = CreatCodeBarByZXing(value, nBarWidth, nBarHeight);
            image.Save(bmpPath, System.Drawing.Imaging.ImageFormat.Jpeg);
            if (File.Exists(bmpPath))
            {
                strLog = string.Format("Gereate {0}", bmpPath);
                Log.WriteAsync(strLog);
            }
            else
            {
                strLog = string.Format("Gereate {0} Fail!", bmpPath);
                Log.WriteAsync(strLog);      
                return false;
            }

            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            //将图片文件读入一个字符串
            byte[] bytes = Bitmap2ByteArray(image);
            int pictureIdx = sheet.Workbook.AddPicture(bytes, PictureType.JPEG);

            // 插图片的位置  
            int dx1 = 50, dy1 = 20, dx2 = 1023, dy2 = 255;
            int col1 = t0.Item1, row1 = t0.Item2, col2 = t1.Item1, row2 = t1.Item2;

            IClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
            //把图片插到相应的位置
            IPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            return true;
        }

        public bool InsertPicture(RangeItemInfo item, Bitmap image)
        {
            string strStart = item.Start;
            string strEnd = item.End;
            string strLog = "";
            if (!CheckRangeNameIsVaild(strStart) || !CheckRangeNameIsVaild(strEnd))
            {
                strLog = string.Format("The Range of {0} is illegal", item.Name);
                Log.WriteAsync(strLog);
                return false;
            }

            Tuple<int, int> t0 = ConvertToColumnRowIndex(strStart);
            Tuple<int, int> t1 = ConvertToColumnRowIndex(strEnd);

            IRow row = sheet.GetRow(t0.Item2);
            ICell cell = row.GetCell(t0.Item1);

            if (cell == null)
            {
                string logInfo = string.Format("找不到位置为{0}:{1}处的单元格.", strStart, strEnd);
                Log.WriteAsync(logInfo);
                return false;
            }

            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            //将图片文件读入一个字符串
            byte[] bytes = Bitmap2ByteArray(image);
            int pictureIdx = sheet.Workbook.AddPicture(bytes, PictureType.JPEG);

            // 插图片的位置  
            int dx1 = 50, dy1 = 20, dx2 = 1023, dy2 = 255;
            int col1 = t0.Item1, row1 = t0.Item2, col2 = t1.Item1, row2 = t1.Item2;

            IClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
            
            //把图片插到相应的位置 IPicture pict = (HSSFPicture)
            patriarch.CreatePicture(anchor, pictureIdx);
            return true;
        }

        private static Bitmap CreatCodeBarByZXing(string code, int width, int height)
        {
            BarcodeWriter writer = new BarcodeWriter();
            writer.Format = BarcodeFormat.CODE_128;
            EncodingOptions options = new EncodingOptions()
            {
                Width = width,
                Height = height,
                Margin = 2,
                PureBarcode = true
            };
            writer.Options = options;
            Bitmap map = writer.Write(code);
            return map;
        }

        public static byte[] Bitmap2ByteArray(Bitmap img)
        {
            using (MemoryStream stream = new MemoryStream())
            {
                img.Save(stream, ImageFormat.Jpeg);
                byte[] data = new byte[stream.Length];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(data, 0, Convert.ToInt32(stream.Length));
                return data;
            }
        }
    }
}
